# coding: utf-8
from sqlalchemy import Column, Integer, String, Text, BigInteger, DateTime, Boolean, SmallInteger, Numeric, ForeignKey, \
    TIMESTAMP
from sqlalchemy.orm import relationship
from sqlalchemy.schema import FetchedValue
from sqlalchemy.ext.declarative import declarative_base



Base = declarative_base()
metadata = Base.metadata

class Numberrange(Base):
    __tablename__ = 'numberrange'

    range_id = Column(BigInteger, primary_key=True)
    number_prefix = Column(String(2))
    number_from = Column(String(6))
    number_to = Column(String(6))
    numer_current = Column(String(6))
    creation_by = Column(String(16))
    creation_on = Column(DateTime)
    change_by = Column(String(16))
    change_on = Column(DateTime)


class Order(Base):
    __tablename__ = 'order'

    order_id = Column(String(255), primary_key=True, comment='使用订货单类型配置的号码范围（numberrange）,关联字段：order.ordery_type, ordertype.range_id, numberrange.range_id')
    order_type = Column(BigInteger)
    order_by = Column(String(16))
    price_list_id = Column(BigInteger)
    partner_id = Column(String(16))
    ship_to_id = Column(BigInteger)
    delivery_date = Column(DateTime, comment='送货日期')
    is_paied = Column(Boolean, comment='付款后更新此标识')
    order_ref = Column(String(16))
    status = Column(String(16))
    creation_on = Column(DateTime)
    creation_by = Column(String(16))
    change_on = Column(DateTime)
    change_by = Column(String(16))


class Price(Base):
    __tablename__ = 'price'

    price_list_id = Column(BigInteger, primary_key=True)
    price_list_type = Column(String(16), comment='product-产品\r\ngroup-price_group\r\ndiscount-基于出厂价的折扣')
    price_list_name = Column(String(16))
    is_activate = Column(Boolean)
    creation_on = Column(DateTime)
    creation_by = Column(String(16))
    change_on = Column(DateTime)
    change_by = Column(String(16))


class Product(Base):
    __tablename__ = 'product'

    product_code = Column(String(16), primary_key=True, comment='必须与ERP保持一致，订货系统前台没有创建产品的权限   需同步数据')
    product_name = Column(String(40))
    product_type = Column(String(8))
    base_unit = Column(String(8), comment='基本的订价、库存、成本计算单位')
    order_unit = Column(String(8))
    barcode = Column(String(13))
    product_image = Column(String(255))
    product_longtext = Column(String(255))
    mini_order_qty = Column(BigInteger)
    product_group1 = Column(BigInteger)
    product_group2 = Column(BigInteger)
    list_price = Column(Numeric(10, 2))
    price_group = Column(SmallInteger)
    product_spec = Column(String(20))
    status = Column(String(16))
    creation_on = Column(DateTime)
    creation_by = Column(String(16))
    change_on = Column(DateTime)
    change_by = Column(String(16))


class Product2packaging(Base):
    __tablename__ = 'product2packaging'

    product_code = Column(String(16))
    id = Column(Integer, primary_key=True)
    pack_unit = Column(String(8))
    qty = Column(BigInteger)
    base_unit = Column(String(8))
    creation_on = Column(DateTime)
    creation_by = Column(String(16))
    change_by = Column(String(16))
    cahnge_on = Column(DateTime)


class Productgroup(Base):
    __tablename__ = 'productgroup'

    product_group = Column(BigInteger, primary_key=True)
    name = Column(String(16))
    creation_on = Column(DateTime)
    creation_by = Column(String(16))
    change_on = Column(DateTime)
    change_by = Column(String(16))


class Reasoncode(Base):
    __tablename__ = 'reasoncode'

    reason_code = Column(String(6))
    id = Column(BigInteger, primary_key=True)
    name = Column(String(16))
    creation_on = Column(DateTime)
    creation_by = Column(String(16))
    change_by = Column(String(16))
    change_on = Column(DateTime)


class Usergroup(Base):
    __tablename__ = 'usergroup'

    user_group = Column(BigInteger, primary_key=True)
    name = Column(String(16))
    change_by = Column(String(16))
    change_on = Column(DateTime)
    creation_on = Column(DateTime)
    creation_by = Column(String(16))


class OrderTemplate(Base):
    __tablename__ = 'order_template'

    template_id = Column(BigInteger, primary_key=True)
    tempalte_name = Column(String(16))
    product_code = Column(ForeignKey('product.product_code', ondelete='CASCADE', onupdate='CASCADE'))
    order_qty = Column(BigInteger)
    base_qty = Column(BigInteger, comment='根据产品包装单位的转换关系进行自动转换')
    base_unit = Column(String(16))
    pack_unit = Column(String(16))
    pack_qty = Column(BigInteger, comment='根据产品包装单位的转换关系进行自动转换')
    creation_on = Column(DateTime)
    creation_by = Column(String(16))
    change_on = Column(DateTime)
    change_by = Column(String(16))

    product = relationship('Product')


class User2template(Base):
    __tablename__ = 'user2template'

    user_id = Column(ForeignKey('users.user_id', ondelete='CASCADE', onupdate='CASCADE'), nullable=False)
    template_id = Column(ForeignKey('order_template.template_id', ondelete='CASCADE', onupdate='CASCADE'), nullable=False)
    creation_on = Column(DateTime)
    creation_by = Column(String(16))
    change_by = Column(String(16))
    change_on = Column(DateTime)
    id = Column(BigInteger, primary_key=True)

    template = relationship('OrderTemplate')
    user = relationship('User')


class Orderitem(Base):
    __tablename__ = 'orderitems'

    id = Column(BigInteger, primary_key=True)
    order_id = Column(ForeignKey('order.order_id', ondelete='CASCADE', onupdate='CASCADE'))
    product_code = Column(String(16))
    order_qty = Column(BigInteger)
    order_unit = Column(String(8))
    price_unit = Column(Numeric(10, 2))
    pack_qty = Column(BigInteger)
    price_subtotal = Column(Numeric(10, 2))
    received_qty = Column(BigInteger)
    discrepancy_qty = Column(BigInteger)
    reconcile_qty = Column(BigInteger)
    change_by = Column(String(16))
    cahnge_on = Column(DateTime)
    creation_on = Column(DateTime)
    creation_by = Column(String(16))

    order = relationship('Order')


class Ordertype(Base):
    __tablename__ = 'ordertype'

    order_type = Column(BigInteger, primary_key=True)
    name = Column(String(16))
    range_id = Column(ForeignKey('numberrange.range_id', ondelete='CASCADE', onupdate='CASCADE'), comment='用来区分号码范围, 不同的订货类型，在配置时指定了号码范围，这个号码范围需要事先配置')
    creation_by = Column(String(16))
    creation_on = Column(DateTime)
    change_on = Column(DateTime)
    change_by = Column(String(16))

    range = relationship('Numberrange')


class Priceitem(Base):
    __tablename__ = 'priceitems'

    price_list_id = Column(ForeignKey('price.price_list_id', ondelete='CASCADE', onupdate='CASCADE'), nullable=False)
    id = Column(Integer, primary_key=True)
    product_code = Column(ForeignKey('product.product_code', ondelete='CASCADE', onupdate='CASCADE'), comment='price_list_type为product的时候输入')
    price = Column(Numeric(10, 2))
    base_unit = Column(ForeignKey('product2packaging.id', ondelete='CASCADE', onupdate='CASCADE'))
    price_group = Column(BigInteger, comment='price_list_type为price_group的时候输入')
    discount = Column(BigInteger, comment='price_list_type为discount的时候输入')
    creation_on = Column(DateTime)
    creation_by = Column(String(16))
    change_on = Column(DateTime)
    change_by = Column(String(16))

    product2packaging = relationship('Product2packaging')
    price_list = relationship('Price')
    product = relationship('Product')


class User(Base):
    __tablename__ = 'users'

    user_id = Column(BigInteger, primary_key=True)
    user_name = Column(String(16), comment='用户名')
    password = Column(String(255), comment='密码')
    user_group = Column(BigInteger,comment="用户组")
    partner_id = Column(String(16), comment='ERP客户号码')
    partner_name = Column(String(40))
    phone_number = Column(String(11), comment='电话号码')
    openid = Column(String(100), comment='小程序openid')
    is_admin = Column(Boolean, nullable=False, comment='是否是管理员')
    creation_by = Column(String(16))
    creation_on = Column(DateTime)
    change_by = Column(String(16))
    change_on = Column(DateTime)



class User2partner(Base):
    __tablename__ = 'user2partner'

    user_id = Column(ForeignKey('users.user_id', ondelete='CASCADE', onupdate='CASCADE'), nullable=False)
    partner_id = Column(BigInteger, primary_key=True)
    is_payonline = Column(Boolean, comment='是否在线支付')
    credit_point = Column(Numeric(10, 2), comment='在后台输入信用额度，需要在线支付的客户有未结算订单时，从额度中扣除未接款数额')
    price_list_id = Column(ForeignKey('price.price_list_id', ondelete='CASCADE', onupdate='CASCADE'), comment='价格表')
    change_by = Column(String(16))
    change_on = Column(DateTime)
    creation_by = Column(String(16))
    creation_on = Column(DateTime)

    price_list = relationship('Price')
    user = relationship('User')


class Users2authorization(Base):
    __tablename__ = 'users2authorization'

    user_id = Column(ForeignKey('users.user_id', ondelete='CASCADE', onupdate='CASCADE'), nullable=False, comment='用户id')
    product_types = Column(String(16), comment='订货选择产品时，分类列表只显示允许的产品类型')
    display_price = Column(Boolean, comment='是否显示价格')
    order_types = Column(ForeignKey('ordertype.order_type', ondelete='CASCADE', onupdate='CASCADE'), comment='创建订货单时，只能选择允许的订货类型')
    manual_delivery = Column(Boolean, comment='手动创建交货单')
    product_group = Column(ForeignKey('productgroup.product_group', ondelete='CASCADE', onupdate='CASCADE'), comment='订货选择产品时，分类列表只显示允许的产品组')
    diff_approval = Column(Boolean, comment='批准对账差异')
    id = Column(BigInteger, primary_key=True)
    creation_by = Column(String(16))
    creation_on = Column(DateTime)
    change_by = Column(String(16))
    change_on = Column(DateTime)

    ordertype = relationship('Ordertype')
    productgroup = relationship('Productgroup')
    user = relationship('User')


class Shipto(Base):
    __tablename__ = 'shipto'

    id = Column(BigInteger, primary_key=True)
    partner_id = Column(ForeignKey('user2partner.partner_id', ondelete='CASCADE', onupdate='CASCADE'))
    ship_id = Column(BigInteger, comment='地址id')
    ship_to_address = Column(String(100), comment='发货地址')
    contact_person = Column(String(16), comment='联系人')
    contact_number = Column(String(11), comment='联系电话')
    change_by = Column(String(16))
    change_on = Column(DateTime)
    creation_on = Column(DateTime)
    creation_by = Column(String(16))

    partner = relationship('User2partner')
